﻿Public Class RepresentanteFarmaciaDAO
    Public Shared Function insertarRepresentanteFarmacia(ByVal PersonaNombre As String, ByVal PersonaApellido As String, ByVal PersonaNroDocumento As Integer, ByRef TipoDocumentoId As Integer, ByVal PersonaFechaNacimiento As String, ByVal PersonaSexo As String, ByVal PersonaTelefono As String, ByVal PersonaEmail As String, ByVal AreaRepresentanteId As Integer, ByVal RepresentanteFarmaciaResponsabilidad As String, ByVal FarmaciaId As Integer, ByVal PaisId As Integer, ByVal ProvinciaId As Integer, ByVal LocalidadId As Integer, ByVal PersonaXDomicilioCalle As String, ByVal PersonaXDomicilioNro As Integer, ByVal PersonaXDomicilioPiso As Integer, ByVal PersonaXDomicilioDepartamento As String, ByVal PersonaXDomicilioBarrio As String, ByVal PersonaXDomicilioObservacion As String, ByVal PersonaXDomicilioCodigoPostal As String, ByVal PersonaCelular As String) As Boolean
        Dim cn As SqlClient.SqlConnection = Nothing
        Try
            Constante.esNulo(PersonaNombre)
            Constante.esNulo(PersonaApellido)
            Constante.esNulo(PersonaSexo)
            Constante.esNulo(PersonaTelefono)
            Constante.esNulo(PersonaCelular)
            Constante.esNulo(PersonaEmail)
            Constante.esNulo(RepresentanteFarmaciaResponsabilidad)
            Constante.esNulo(PersonaXDomicilioCalle)
            Constante.esNulo(PersonaXDomicilioDepartamento)
            Constante.esNulo(PersonaXDomicilioBarrio)
            Constante.esNulo(PersonaXDomicilioObservacion)
            Constante.esNulo(PersonaXDomicilioDepartamento)
            Constante.esNulo(PersonaXDomicilioCodigoPostal)
            cn = Conexion.Conectar
            Dim consulta As New SqlClient.SqlCommand
            With consulta
                .CommandText = "usp_RepresentanteFarmacia"
                .CommandType = CommandType.StoredProcedure
                With .Parameters
                    .AddWithValue("@Accion", Constante.INSERTAR).DbType = DbType.Int32
                    .AddWithValue("@RepresentanteFarmaciaId", Constante.MENOSUNO).DbType = DbType.Int32
                    .AddWithValue("@PersonaNombre", PersonaNombre).DbType = DbType.String
                    .AddWithValue("@PersonaApellido", PersonaApellido).DbType = DbType.String
                    .AddWithValue("@PersonaNroDocumento", PersonaNroDocumento).DbType = DbType.Int32
                    .AddWithValue("@TipoDocumentoId", TipoDocumentoId).DbType = DbType.Int32
                    .AddWithValue("@PersonaFechaNacimiento", Constante.FECHAVACIA).DbType = DbType.Date
                    .AddWithValue("@PersonaSexo", PersonaSexo).DbType = DbType.String
                    .AddWithValue("@PersonaTelefono", PersonaTelefono).DbType = DbType.String
                    .AddWithValue("@PersonaCelular", PersonaCelular).DbType = DbType.String
                    .AddWithValue("@PersonaEmail", PersonaEmail).DbType = DbType.String
                    .AddWithValue("@AreaRepresentanteId", AreaRepresentanteId).DbType = DbType.Int32
                    .AddWithValue("@RepresentanteFarmaciaResponsabilidad", RepresentanteFarmaciaResponsabilidad).DbType = DbType.String
                    .AddWithValue("@FarmaciaId", FarmaciaId).DbType = DbType.Int32
                    .AddWithValue("@PersonaId", Constante.MENOSUNO).DbType = DbType.Int32
                    .AddWithValue("@PaisId", PaisId).DbType = DbType.Int32
                    .AddWithValue("@ProvinciaId", ProvinciaId).DbType = DbType.Int32
                    .AddWithValue("@LocalidadId", LocalidadId).DbType = DbType.Int32
                    .AddWithValue("@PersonaXDomicilioCalle", PersonaXDomicilioCalle).DbType = DbType.String
                    .AddWithValue("@PersonaXDomicilioId", Constante.MENOSUNO).DbType = DbType.String
                    .AddWithValue("@PersonaXDomicilioNro", PersonaXDomicilioNro).DbType = DbType.Int32
                    .AddWithValue("@PersonaXDomicilioPiso", PersonaXDomicilioPiso).DbType = DbType.Int32
                    .AddWithValue("@PersonaXDomicilioDepartamento", PersonaXDomicilioDepartamento).DbType = DbType.String
                    .AddWithValue("@PersonaXDomicilioBarrio", PersonaXDomicilioBarrio).DbType = DbType.String
                    .AddWithValue("@PersonaXDomicilioObservacion", PersonaXDomicilioObservacion).DbType = DbType.String
                    .AddWithValue("@PersonaXDomicilioCodigoPostal", PersonaXDomicilioCodigoPostal).DbType = DbType.String
                End With
                .Connection = cn
                .ExecuteNonQuery()
            End With
            Return True
        Catch ex As Exception
            'GUARDAMOS EL ERROR EN LA BD
            ErrorDAO.insertarError(ex)
            Return False
        Finally
            Conexion.Desconectar(cn) 'PASAR POR PARAMETRO LA CONEXION
        End Try
    End Function

    Public Shared Function obtenerRepresentanteFarmacias(ByVal RepresentanteFarmaciaId As Integer) As DataTable
        Dim cn As SqlClient.SqlConnection = Nothing
        Try
            cn = Conexion.Conectar
            Dim ds As New DataSet
            Dim da As New SqlClient.SqlDataAdapter("usp_RepresentanteFarmacia_obtener", cn)
            da.SelectCommand.CommandType = CommandType.StoredProcedure
            da.SelectCommand.Parameters.AddWithValue("@RepresentanteFarmaciaId", RepresentanteFarmaciaId).DbType = DbType.Int32
            da.Fill(ds, "RepresentantesFarmacias")

            Return ds.Tables("RepresentantesFarmacias")
        Catch ex As Exception
            'GUARDAMOS EL ERROR EN LA BD
            ErrorDAO.insertarError(ex)
            Return Nothing
        Finally
            Conexion.Desconectar(cn) 'PASAR POR PARAMETRO LA CONEXION
        End Try
    End Function

    Public Shared Function obtenerRepresentanteFarmaciasDs(ByVal RepresentanteFarmaciaId As Integer) As DataSet
        Dim cn As SqlClient.SqlConnection = Nothing
        Try
            cn = Conexion.Conectar
            Dim ds As New DataSet
            Dim da As New SqlClient.SqlDataAdapter("usp_RepresentanteFarmacia_combo", cn)
            da.SelectCommand.CommandType = CommandType.StoredProcedure
            da.Fill(ds, "RepresentantesFarmacias")

            Return ds
        Catch ex As Exception
            'GUARDAMOS EL ERROR EN LA BD
            ErrorDAO.insertarError(ex)
            Return Nothing
        Finally
            Conexion.Desconectar(cn) 'PASAR POR PARAMETRO LA CONEXION
        End Try
    End Function

    Public Shared Function obtenerEmailRepresentante(ByVal RepresentanteFarmaciaId As Integer) As String
        Dim cn As SqlClient.SqlConnection = Nothing
        Try

            cn = Conexion.Conectar
            Dim consulta As New SqlClient.SqlCommand
            Dim PersonaEmail As String = ""
            With consulta
                .CommandText = "Select P.PersonaEmail From Persona P, RepresentanteFarmacia R Where R.PersonaId = P.PersonaId And R.RepresentanteFarmaciaId = " + RepresentanteFarmaciaId.ToString()
                .Connection = cn
                PersonaEmail = .ExecuteScalar()
            End With

            Return PersonaEmail
        Catch ex As Exception
            'GUARDAMOS EL ERROR EN LA BD
            ErrorDAO.insertarError(ex)
            Return False
        Finally
            Conexion.Desconectar(cn) 'PASAR POR PARAMETRO LA CONEXION
        End Try
    End Function

    Public Shared Function actualizarRepresentanteFarmacia(ByVal PersonaId As Integer, ByVal RepresentanteFarmaciaId As Integer, ByVal PersonaNombre As String, ByVal PersonaApellido As String, ByVal PersonaNroDocumento As Integer, ByRef TipoDocumentoId As Integer, ByVal PersonaFechaNacimiento As Date, ByVal PersonaSexo As String, ByVal PersonaTelefono As String, ByVal PersonaEmail As String, ByVal AreaRepresentanteId As Integer, ByVal RepresentanteFarmaciaResponsabilidad As String, ByVal FarmaciaId As Integer, ByVal PaisId As Integer, ByVal ProvinciaId As Integer, ByVal LocalidadId As Integer, ByVal PersonaXDomicilioCalle As String, ByVal PersonaXDomicilioNro As Integer, ByVal PersonaXDomicilioPiso As Integer, ByVal PersonaXDomicilioDepartamento As String, ByVal PersonaXDomicilioBarrio As String, ByVal PersonaXDomicilioObservacion As String, ByVal PersonaXDomicilioCodigoPostal As String, ByVal PersonaCelular As String) As Boolean
        Dim cn As SqlClient.SqlConnection = Nothing
        Try
            Constante.esNulo(PersonaNombre)
            Constante.esNulo(PersonaApellido)
            Constante.esNulo(PersonaSexo)
            Constante.esNulo(PersonaTelefono)
            Constante.esNulo(PersonaCelular)
            Constante.esNulo(PersonaEmail)
            Constante.esNulo(RepresentanteFarmaciaResponsabilidad)
            Constante.esNulo(PersonaXDomicilioCalle)
            Constante.esNulo(PersonaXDomicilioDepartamento)
            Constante.esNulo(PersonaXDomicilioBarrio)
            Constante.esNulo(PersonaXDomicilioObservacion)
            Constante.esNulo(PersonaXDomicilioDepartamento)
            Constante.esNulo(PersonaXDomicilioCodigoPostal)
            cn = Conexion.Conectar
            Dim consulta As New SqlClient.SqlCommand
            With consulta
                .CommandText = "usp_RepresentanteFarmacia"
                .CommandType = CommandType.StoredProcedure
                With .Parameters
                    .AddWithValue("@Accion", Constante.ACTUALIZAR).DbType = DbType.Int32
                    .AddWithValue("@RepresentanteFarmaciaId", RepresentanteFarmaciaId).DbType = DbType.Int32
                    .AddWithValue("@PersonaNombre", PersonaNombre).DbType = DbType.String
                    .AddWithValue("@PersonaApellido", PersonaApellido).DbType = DbType.String
                    .AddWithValue("@PersonaNroDocumento", PersonaNroDocumento).DbType = DbType.Int32
                    .AddWithValue("@TipoDocumentoId", TipoDocumentoId).DbType = DbType.Int32
                    .AddWithValue("@PersonaFechaNacimiento", PersonaFechaNacimiento).DbType = DbType.Date
                    .AddWithValue("@PersonaSexo", PersonaSexo).DbType = DbType.String
                    .AddWithValue("@PersonaTelefono", PersonaTelefono).DbType = DbType.String
                    .AddWithValue("@PersonaCelular", PersonaCelular).DbType = DbType.String
                    .AddWithValue("@PersonaEmail", PersonaEmail).DbType = DbType.String
                    .AddWithValue("@AreaRepresentanteId", AreaRepresentanteId).DbType = DbType.Int32
                    .AddWithValue("@RepresentanteFarmaciaResponsabilidad", RepresentanteFarmaciaResponsabilidad).DbType = DbType.String
                    .AddWithValue("@FarmaciaId", FarmaciaId).DbType = DbType.Int32
                    .AddWithValue("@PersonaId", PersonaId).DbType = DbType.Int32
                    .AddWithValue("@PaisId", PaisId).DbType = DbType.Int32
                    .AddWithValue("@ProvinciaId", ProvinciaId).DbType = DbType.Int32
                    .AddWithValue("@LocalidadId", LocalidadId).DbType = DbType.Int32
                    .AddWithValue("@PersonaXDomicilioId", Constante.MENOSUNO).DbType = DbType.String
                    .AddWithValue("@PersonaXDomicilioCalle", PersonaXDomicilioCalle).DbType = DbType.String
                    .AddWithValue("@PersonaXDomicilioNro", PersonaXDomicilioNro).DbType = DbType.Int32
                    .AddWithValue("@PersonaXDomicilioPiso", PersonaXDomicilioPiso).DbType = DbType.Int32
                    .AddWithValue("@PersonaXDomicilioDepartamento", PersonaXDomicilioDepartamento).DbType = DbType.String
                    .AddWithValue("@PersonaXDomicilioBarrio", PersonaXDomicilioBarrio).DbType = DbType.String
                    .AddWithValue("@PersonaXDomicilioObservacion", PersonaXDomicilioObservacion).DbType = DbType.String
                    .AddWithValue("@PersonaXDomicilioCodigoPostal", PersonaXDomicilioCodigoPostal).DbType = DbType.String
                End With
                .Connection = cn
                .ExecuteNonQuery()

            End With
            Return True
        Catch ex As Exception
            'GUARDAMOS EL ERROR EN LA BD
            ErrorDAO.insertarError(ex)
            Return False
        Finally
            Conexion.Desconectar(cn) 'PASAR POR PARAMETRO LA CONEXION
        End Try
    End Function

    Public Shared Function eliminarRepresentanteFarmacia(ByVal RepresentanteFarmaciaId As Integer) As Boolean
        Dim cn As SqlClient.SqlConnection = Nothing
        Try
            cn = Conexion.Conectar
            Dim consulta As New SqlClient.SqlCommand
            With consulta
                .CommandText = "usp_RepresentanteFarmacia"
                .CommandType = CommandType.StoredProcedure
                With .Parameters
                    .AddWithValue("@Accion", Constante.ELIMINAR).DbType = DbType.Int32
                    .AddWithValue("@RepresentanteFarmaciaId", RepresentanteFarmaciaId).DbType = DbType.Int32
                    .AddWithValue("@PersonaNombre", Constante.VACIO).DbType = DbType.String
                    .AddWithValue("@PersonaApellido", Constante.VACIO).DbType = DbType.String
                    .AddWithValue("@PersonaNroDocumento", Constante.VACIO).DbType = DbType.String
                    .AddWithValue("@TipoDocumentoId", Constante.MENOSUNO).DbType = DbType.Int32
                    .AddWithValue("@PersonaFechaNacimiento", Constante.FECHAVACIA).DbType = DbType.Date
                    .AddWithValue("@PersonaSexo", Constante.VACIO).DbType = DbType.String
                    .AddWithValue("@PersonaTelefono", Constante.VACIO).DbType = DbType.String
                    .AddWithValue("@PersonaCelular", Constante.VACIO).DbType = DbType.String
                    .AddWithValue("@PersonaEmail", Constante.VACIO).DbType = DbType.String
                    .AddWithValue("@AreaRepresentanteId", Constante.VACIO).DbType = DbType.String
                    .AddWithValue("@RepresentanteFarmaciaResponsabilidad", Constante.VACIO).DbType = DbType.String
                    .AddWithValue("@FarmaciaId", Constante.MENOSUNO).DbType = DbType.Int32
                    .AddWithValue("@PersonaId", Constante.MENOSUNO).DbType = DbType.Int32
                    .AddWithValue("@PaisId", Constante.MENOSUNO).DbType = DbType.Int32
                    .AddWithValue("@ProvinciaId", Constante.MENOSUNO).DbType = DbType.Int32
                    .AddWithValue("@LocalidadId", Constante.MENOSUNO).DbType = DbType.Int32
                    .AddWithValue("@PersonaXDomicilioId", Constante.MENOSUNO).DbType = DbType.String
                    .AddWithValue("@PersonaXDomicilioCalle", Constante.VACIO).DbType = DbType.String
                    .AddWithValue("@PersonaXDomicilioNro", Constante.MENOSUNO).DbType = DbType.Int32
                    .AddWithValue("@PersonaXDomicilioPiso", Constante.MENOSUNO).DbType = DbType.Int32
                    .AddWithValue("@PersonaXDomicilioDepartamento", Constante.VACIO).DbType = DbType.String
                    .AddWithValue("@PersonaXDomicilioBarrio", Constante.VACIO).DbType = DbType.String
                    .AddWithValue("@PersonaXDomicilioObservacion", Constante.VACIO).DbType = DbType.String
                    .AddWithValue("@PersonaXDomicilioCodigoPostal", Constante.VACIO).DbType = DbType.String
                End With
                .Connection = cn
                .ExecuteNonQuery()
            End With

            Return True
        Catch ex As Exception
            'GUARDAMOS EL ERROR EN LA BD
            ErrorDAO.insertarError(ex)
            Return False
        Finally
            Conexion.Desconectar(cn) 'PASAR POR PARAMETRO LA CONEXION
        End Try
    End Function

    Public Shared Function selectRepresentanteFarmacias(ByVal FarmaciaId As Integer) As DataTable
        Dim cn As SqlClient.SqlConnection = Nothing
        Try
            cn = Conexion.Conectar
            Dim ds As New DataSet
            Dim da As New SqlClient.SqlDataAdapter("usp_RepresentanteFarmacia_select", cn)
            da.SelectCommand.CommandType = CommandType.StoredProcedure
            da.SelectCommand.Parameters.AddWithValue("@FarmaciaId", FarmaciaId).DbType = DbType.Int32
            da.Fill(ds, "RepresentantesFarmacias")

            Return ds.Tables("RepresentantesFarmacias")
        Catch ex As Exception
            'GUARDAMOS EL ERROR EN LA BD
            ErrorDAO.insertarError(ex)
            Return Nothing
        Finally
            Conexion.Desconectar(cn) 'PASAR POR PARAMETRO LA CONEXION
        End Try
    End Function

    Public Shared Function comboRepresentanteFarmacia() As DataTable
        Dim cn As SqlClient.SqlConnection = Nothing
        Try
            cn = Conexion.Conectar
            Dim ds As New DataSet
            Dim da As New SqlClient.SqlDataAdapter("usp_RepresentanteFarmacia_combo", cn)
            da.SelectCommand.CommandType = CommandType.StoredProcedure
            da.Fill(ds, "RepresentantesFarmacias")

            Return ds.Tables("RepresentantesFarmacias")
        Catch ex As Exception
            'GUARDAMOS EL ERROR EN LA BD
            ErrorDAO.insertarError(ex)
            Return Nothing
        Finally
            Conexion.Desconectar(cn) 'PASAR POR PARAMETRO LA CONEXION
        End Try
    End Function
End Class
